USE Lib
GO

---------------------------------------------------------------------------
-- BEGIN MAINTENANCE WRAPPER
----------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'sp_join') BEGIN
	DROP PROCEDURE tsql.sp_join
	PRINT '<<< DROP PROCEDURE tsql.sp_join - Completed with SUCCESS >>>'
END

GO
----------------------------
-- END MAINTENANCE WRAPPER
---------------------------------------------------------------------------

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


/*================================================================================================
Name:			tsql.sp_join
Type:			User defined procedure 
Owner:			
Description:	implements similar functionality with JOIN function in VB
Parameters:		@sql = source select statement  
				@delimiter = character to use as delimiter for result string
				@string = procedures output string
Dependens on:  	none
Usage:			
--------------------------------------------------------------------------------------------------
Project:		http://sqllib.codeplex.com/
Help:			http://sqllib.codeplex.com/wikipage?title=sp_join&referringTitle=Documentation		
Version:		20120822
SrcCtrlApp:		SVN
SrcCtrlPath:	http://sqllib.codeplex.com/SourceControl/list/changesets
--------------------------------------------------------------------------------------------------
History:		06/22/2007 - Adrian E Dudau - Procedure created
History:		08/22/2012 - Adrian E Dudau - Added references to project, SVN and Help
================================================================================================*/

CREATE PROCEDURE tsql.[sp_join]
	(
	@sql VARCHAR(8000) ,
	@delimiter CHAR(1) = ';', 
	@string VARCHAR(8000) = '' OUTPUT  
	)
AS
BEGIN
	DECLARE @ParVal VARCHAR(8000)
	DECLARE @Buffer NVARCHAR(4000)
	DECLARE @my_cur CURSOR

	SET @string = ''
	SET @Buffer = N'SET @my_cur = CURSOR STATIC FOR ' + @sql + '; OPEN @my_cur;' 

	EXEC sp_executesql
		 @Buffer,
		 N'@my_cur cursor OUTPUT', @my_cur OUTPUT
	FETCH NEXT FROM @my_cur INTO @ParVal 

	WHILE @@FETCH_STATUS = 0
	BEGIN
		IF @string = '' 
			SET @string = @ParVal
		ELSE
			SET @string = @string + @delimiter + @ParVal

		FETCH NEXT FROM @my_cur INTO @ParVal 
	END

	CLOSE @my_cur
	DEALLOCATE @my_cur
END
GO

---------------------------------------------------------------------------
-- BEGIN MAINTENANCE WRAPPER
-- Verify completion
----------------------------
IF EXISTS(SELECT * FROM sysobjects WHERE type = 'P' AND name = 'sp_join') BEGIN 
	PRINT '<<< CREATE PROCEDURE tsql.sp_join - Completed with SUCCESS >>>'
END

ELSE BEGIN 
	PRINT '<<< CREATE PROCEDURE tsql.sp_join - Completed with ERROR >>>'
END


GO
----------------------------
-- END MAINTENANCE WRAPPER
---------------------------------------------------------------------------

--  GRANT SELECT TO PUBLIC will be used at Db level
